IF OBJECT_ID('dbo.bc_sp_Sys_Get_RefDesc') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.bc_sp_Sys_Get_RefDesc
    IF OBJECT_ID('dbo.bc_sp_Sys_Get_RefDesc') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.bc_sp_Sys_Get_RefDesc >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.bc_sp_Sys_Get_RefDesc >>>'
END
go
create procedure dbo.bc_sp_Sys_Get_RefDesc
	@data_name		char(32),
	@data_code		char(15),
	@ref_group_cd	     char(6),
	@language_id	     smallint,
	@category_cd	     char(6),
	@code_eff_date      smalldatetime,
	@category_type_cd   char(3),
	@corp_equiv_cd   	char(15)
as

if not exists(select * from TREFTAB
                      where data_element_name = @data_name
                        and (data_element_code = @data_code or rtrim(@data_code) = null)
                        and ref_group_cd in (@ref_group_cd,'729000')   --2008/07/31 kay.yu add 729000 for Goald Consolidation
                        and language_id = @language_id
                        and (category_cd = @category_cd or rtrim(@category_cd) = null)
				    and @code_eff_date between code_eff_date and code_exp_date
                        and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
                        and (corp_equiv_cd = @corp_equiv_cd or rtrim(@corp_equiv_cd) = null))
begin
	select @language_id = 1
end

IF not (rtrim(@data_code) = null AND rtrim(@corp_equiv_cd) = null)

--jianxing ITSR005384
/*
    select ele_value_desc_txt, corp_equiv_cd
  	from TREFTAB
 	where data_element_name = @data_name
   	and (data_element_code = @data_code or rtrim(@data_code) = null)
    and ref_group_cd in (@ref_group_cd,'729000')   --2008/07/31 kay.yu add 729000 for Goald Consolidation
   	and language_id = @language_id
   	and (category_cd = @category_cd or rtrim(@category_cd) = null)
   	and @code_eff_date between code_eff_date and code_exp_date
   	and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
   	and (corp_equiv_cd = @corp_equiv_cd or rtrim(@corp_equiv_cd) = null)
*/
 if @data_name = 'ENDORSEMENT-REASON-CODE'
     select ele_value_desc_txt, corp_equiv_cd
  	from TREFTAB
 	where data_element_name = @data_name
   	and (data_element_code = @data_code or rtrim(@data_code) = null)
    and ref_group_cd =@ref_group_cd   --2008/07/31 kay.yu add 729000 for Goald Consolidation
   	and language_id = @language_id
   	and (category_cd = @category_cd or rtrim(@category_cd) = null)
   	and @code_eff_date between code_eff_date and code_exp_date
   	and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
   	and (corp_equiv_cd = @corp_equiv_cd or rtrim(@corp_equiv_cd) = null)
    else
     select ele_value_desc_txt, corp_equiv_cd
  	from TREFTAB
 	where data_element_name = @data_name
   	and (data_element_code = @data_code or rtrim(@data_code) = null)
    and ref_group_cd in (@ref_group_cd,'729000')   --2008/07/31 kay.yu add 729000 for Goald Consolidation
   	and language_id = @language_id
   	and (category_cd = @category_cd or rtrim(@category_cd) = null)
   	and @code_eff_date between code_eff_date and code_exp_date
   	and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
   	and (corp_equiv_cd = @corp_equiv_cd or rtrim(@corp_equiv_cd) = null)
ELSE
	select '' ele_value_desc_txt , '' corp_equiv_cd WHERE 0 = 1

go
IF OBJECT_ID('dbo.bc_sp_Sys_Get_RefDesc') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.bc_sp_Sys_Get_RefDesc >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.bc_sp_Sys_Get_RefDesc >>>'
go
EXEC sp_procxmode 'dbo.bc_sp_Sys_Get_RefDesc','unchained'
go
GRANT EXECUTE ON dbo.bc_sp_Sys_Get_RefDesc TO pagroup
go
GRANT EXECUTE ON dbo.bc_sp_Sys_Get_RefDesc TO s3bapp
go
GRANT EXECUTE ON dbo.bc_sp_Sys_Get_RefDesc TO s3benq
go
